<?php
echo "==========================================\n";
echo "  优化状态检查\n";
echo "==========================================\n\n";

$pdo = new PDO('mysql:host=localhost;dbname=oa5', 'root', '123456');

// 1. 检查字段
echo "1. 检查业务字段:\n";
$stmt = $pdo->query("
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'oa5'
    AND table_name = 'oa_customer'
    AND column_name IN ('is_member', 'contract_num', 'case_num', 'membership_end_time', 'contract_amount')
    ORDER BY column_name
");
$fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
echo "   已添加字段: " . implode(', ', $fields) . "\n\n";

// 2. 检查触发器
echo "2. 检查触发器:\n";
$stmt = $pdo->query("
    SELECT trigger_name
    FROM information_schema.triggers
    WHERE trigger_schema = 'oa5'
    AND event_object_table IN ('contract', 'lawcase')
    ORDER BY trigger_name
");
$triggers = $stmt->fetchAll(PDO::FETCH_COLUMN);
if (empty($triggers)) {
    echo "   ✗ 未创建触发器\n\n";
} else {
    echo "   ✓ 已创建触发器: " . count($triggers) . " 个\n";
    foreach ($triggers as $t) {
        echo "     - $t\n";
    }
    echo "\n";
}

// 3. 检查数据
echo "3. 检查现有数据:\n";
$stmt = $pdo->query("
    SELECT
        COUNT(*) AS total,
        COUNT(CASE WHEN is_member > 0 THEN 1 END) AS has_member_field,
        COUNT(CASE WHEN contract_num > 0 THEN 1 END) AS has_contract_num,
        COUNT(CASE WHEN case_num > 0 THEN 1 END) AS has_case_num
    FROM oa_customer WHERE delete_time = 0
");
$stats = $stmt->fetch();
echo "   总客户数: " . $stats['total'] . "\n";
echo "   is_member字段有值的客户: " . $stats['has_member_field'] . "\n";
echo "   contract_num字段有值的客户: " . $stats['has_contract_num'] . "\n";
echo "   case_num字段有值的客户: " . $stats['has_case_num'] . "\n\n";

// 4. 显示一些样本数据
echo "4. 样本数据 (前5个客户):\n";
$stmt = $pdo->query("
    SELECT id, name, is_member, contract_num, case_num
    FROM oa_customer
    WHERE delete_time = 0
    LIMIT 5
");
while ($row = $stmt->fetch()) {
    echo "   ID: {$row['id']}, 名称: {$row['name']}, 会员: {$row['is_member']}, 合同: {$row['contract_num']}, 案件: {$row['case_num']}\n";
}

echo "\n==========================================\n";
echo "  检查完成\n";
echo "==========================================\n";
